<?php if (!defined('BASEPATH')) {
	exit('No direct script access allowed');
}

class Reader_progress_model extends CI_Model {
	public function __construct() {
		parent::__construct();
	}

	public function get_status($date) {
		//var_dump($date);
		/*SELECT aea.zone_1 zone_1
										  ,sum(wc.line_amt) bill_watcons
									      ,sum(ur.curchg) bill_amt
									      ,sum(ur.curchg) - sum(wc.line_amt) bill_chrg
									      ,count(ur.curchg) bill_count
									      ,count(*) custs_count
									      ,(count(ur.curchg) * 100 / count(*)) percent_completed
									      ,count(aa.account_id) - count(ur.curchg) pending_count
									      ,sum(qty) qty
									FROM ar_account aa
								    	INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
								    	LEFT OUTER JOIN (SELECT ui.invoice_id,ui.customer_id account_id, ifnull(uil.line_amt,0) line_amt,ifnull(uil.usage_qty,0) qty
								                    FROM ubs_invoices ui
								                   	INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
								                    WHERE uil.charge_code = 'WATCONS'
								                    AND DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
								                    GROUP BY account_id) wc ON wc.account_id = aa.account_id
										LEFT OUTER JOIN (SELECT umr.account_id account_id, ifnull(sum(umr.curchg),0) curchg FROM ubs_meter_readings umr
								                   WHERE CONCAT(umr.bill_year,'-',umr.bill_month) = '".$date."'
								                   GROUP BY umr.account_id) ur ON ur.account_id = aa.account_id
										WHERE aa.enable_flag = 'Y'
								        AND LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m')) BETWEEN LAST_DAY(aa.active_start) AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
									GROUP BY aea.zone_1
								    ORDER BY aea.zone_1 ASC
		$query = $this->db->query("SELECT aea.zone_1 zone_1
										  ,sum(wc.line_amt) bill_watcons
									      ,sum(ur.curchg) bill_amt
									      ,sum(ur.curchg) - sum(wc.line_amt) bill_chrg
									      ,count(ur.curchg) bill_count
									      ,count(*) custs_count
									      ,(count(ur.curchg) * 100 / count(*)) percent_completed
									      ,count(aa.account_id) - count(ur.curchg) pending_count
									      ,sum(qty) qty
									FROM ar_account aa
								    	INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
								    	LEFT JOIN (SELECT ui.invoice_id,ui.customer_id account_id, ifnull(sum(uil.line_amt),0) line_amt,ifnull(uil.usage_qty,0) qty
								                    FROM ubs_invoices ui
								                   	INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
								                    WHERE uil.charge_code =	'WATCONS'
								                    AND ui.invoice_num IN (SELECT bill_id FROM ubs_meter_readings)
								                    AND DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
								                    GROUP BY account_id) wc ON wc.account_id = aa.account_id
										LEFT JOIN (SELECT umr.account_id account_id, ifnull(sum(umr.curchg),0) curchg FROM ubs_meter_readings umr
								                   WHERE CONCAT(umr.bill_year,'-',umr.bill_month) = '".$date."'
								                   GROUP BY umr.account_id) ur ON ur.account_id = aa.account_id
										WHERE aa.enable_flag = 'Y'
								        AND LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m')) BETWEEN LAST_DAY(aa.active_start) AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
									GROUP BY aea.zone_1
								    ORDER BY aea.zone_1 ASC");*/
		$sql = "SELECT aea.zone_1 zone_1
               ,COUNT(*) custs_count
               ,COUNT(wc2.curchg) bill_count
               ,COUNT(aa.account_id) - COUNT(wc2.curchg) pending_count
               ,(COUNT(wc2.curchg) * 100 / COUNT(*)) percent_completed
               ,SUM(wc.line_amt) bill_watcons
               ,SUM(wc2.curchg) - SUM(wc.line_amt) bill_chrg
               ,SUM(wc2.curchg) bill_amt
               ,SUM(qty) qty
               ,'".$date."'
               ,(SELECT company_id FROM gl_company)
               ,urtv.reader_id
               ,CONCAT(au.first_name,' ',au.last_name) reader
         FROM ar_account aa
             INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
             JOIN ubs_read_tasks_v urtv ON aa.account_id = urtv.account_id
             JOIN app_users au ON au.user_id = urtv.reader_id
             LEFT JOIN (SELECT ui.invoice_id,ui.customer_id account_id, IFNULL(SUM(uil.line_amt),0) line_amt,IFNULL(uil.usage_qty,0) qty
                            FROM ubs_invoices ui
                            INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
                            WHERE uil.charge_code = 'WATCONS'
                            AND DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                            AND ui.invoice_type='BILL' 
                            GROUP BY account_id) wc ON wc.account_id = aa.account_id
          LEFT JOIN (SELECT ui.customer_id account_id, IFNULL(SUM(uil.line_amt),0) curchg
                            FROM ubs_invoices ui
                            INNER JOIN ubs_invoice_lines uil ON uil.invoice_id = ui.invoice_id
                            WHERE 1=1
                            AND DATE_FORMAT(ui.invoice_date,'%Y-%m') = '".$date."'
                            AND ui.invoice_type='BILL' 
                            GROUP BY account_id) wc2 ON wc2.account_id = aa.account_id
          WHERE aa.enable_flag = 'Y'
		AND CONCAT(urtv.year,'-',urtv.month) = '".$date."'
                AND LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m')) BETWEEN LAST_DAY(aa.active_start) AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
         GROUP BY urtv.reader_id
            ORDER BY aea.zone_1 ASC;";
		//$query = $this->db->query("SELECT * FROM mv_billing_prog WHERE yearMonth = '".$date."' ");
		$query = $this->db->query($sql);
		return $query->result();
	}

	public function get_pnd($date,$zone){
		$query = $this->db->query("SELECT aa.account_id,
								    aa.acct_num,
								    aa.acct_name,
								    aa.status,
								    aea.zone_1,
								    urtv.reader_id
								   	FROM ar_account aa
								   	INNER JOIN ar_entity_address aea ON aea.address_id = aa.acct_addr
								   	JOIN ubs_read_tasks_v urtv ON urtv.`account_id` = aa.`account_id`
								   	WHERE LAST_DAY(STR_TO_DATE('".$date."', '%Y-%m')) BETWEEN LAST_DAY(aa.active_start) 
								    AND IFNULL(LAST_DAY(aa.active_end), LAST_DAY(NOW()))
								    AND aa.enable_flag = 'Y'
								    AND aa.account_id NOT IN (SELECT umr.account_id FROM ubs_meter_readings umr WHERE CONCAT(umr.bill_year,'-',umr.bill_month) = '".$date."'  GROUP BY umr.account_id)
								    AND urtv.reader_id = '".$zone."' ");
		return $query->result();
		//var_dump($query->result());
	}

	public function get_email($type) {
		$this->db->where('mail_interval_type', $type);
		$this->db->where('enable_flag', 'Y');
		$query = $this->db->get('email');
		return $query->result();
	}
	public function update_sentemail($id) {
		$this->db->where('email_id', $id);
		$this->db->update('email', array('is_sent' => 1));
	}
}